package org.jiao.demo.core.jdbc;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;

import java.sql.*;

public class CommonJDBCDao {

	public void test(String sql){

        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://127.0.0.1:3306/rwbi_survey";
        String user = "root"; 
        String password = "root";
        try { 
         Class.forName(driver);
         Connection conn = DriverManager.getConnection(url, user, password);
         if(!conn.isClosed()) 
          System.out.println("Succeeded connecting to the Database!");
         Statement statement = conn.createStatement();

         // 要执行的SQL语句
         // 结果集
         ResultSet rs = statement.executeQuery(sql);

         System.out.println("-----------------");
         System.out.println("执行结果如下所示:");
         System.out.println("-----------------");
         System.out.println(" 学号" + "\t" + " 姓名");
         System.out.println("-----------------");

         String name = null;

         ResultSetMetaData metaData = rs.getMetaData();
//         String columnName = metaData.getColumnName(1);
//         String catalogName = metaData.getCatalogName(1);
//         String columnLabel = metaData.getColumnLabel(1);
//         System.out.println(catalogName);
         while(rs.next()) {
 
          // 选择sname这列数据
          name = rs.getString("surveyName");
 
          // 首先使用ISO-8859-1字符集将name解码为字节序列并将结果存储新的字节数组中。
          // 然后使用GB2312字符集解码指定的字节数组
//          name = new String(name.getBytes("ISO-8859-1"),"GB2312");

          // 输出结果
          System.out.println(rs.getString(2) + "\t" + name);
         }

         rs.close();
         conn.close();

        } catch(ClassNotFoundException e) {


         System.out.println("Sorry,can`t find the Driver!"); 
         e.printStackTrace();


        } catch(SQLException e) {


         e.printStackTrace();


        } catch(Exception e) {


         e.printStackTrace();


        } 
	
	}
	
	public static void main(String[] args){
		CommonJDBCDao jdbcDao = new CommonJDBCDao();
		try {
			String fileDatas = getFileDatas("D:/Projects/survey/Survey/src/com/rwbi/analysis/sql/survey_basedata.sql");
			jdbcDao.test(fileDatas);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static String getFileDatas(String filePath) throws IOException{
		File file = new File(filePath);
		StringBuffer buf = new StringBuffer();
		BufferedReader br=new BufferedReader(new InputStreamReader(new FileInputStream(file),"UTF-8"));
		String line ;
		while((line = br.readLine())!=null){
			buf.append(line).append("\n");
		}
		return buf.toString();
	}
	
}
